package com.situ.personal.dao;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.situ.personal.entity.User;
import com.situ.personal.util.JDBCUtil;
import com.situ.personal.vo.UserDeptVO;

public class UserDao {
	public List<User> selectAll(){
		Connection connection = null;
	    PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		ArrayList<User> list = new ArrayList<User>();
		
	    try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT `id`,`name`,`password`,`email`,`age`,`department_id` FROM `user`";
			preparedStatement = connection.prepareStatement(sql);
			System.out.println(preparedStatement);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				String password = resultSet.getString("password");
				String email = resultSet.getString("email");
				int age = resultSet.getInt("age");
				int departmentId = resultSet.getInt("department_id");
				
				User user = new User(id, name, password, age, email, departmentId); 
				list.add(user);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	    for (User user : list) {
			System.out.println(user);
		}
	    return list;
	    
	}
	public int deleteById(Integer id) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		int count = 0;
		
		try {
			connection = JDBCUtil.getConnection();
			String sql = "DELETE FROM `user` WHERE `id`=?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1,id);
			System.out.println(preparedStatement);
			count = preparedStatement.executeUpdate();
			System.out.println("count" + count);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
		 
	}
	
	public int  insert(User user) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		int count = 0;
		
		try {
			connection = JDBCUtil.getConnection();
			String sql = "INSERT INTO `user` (`name`,`age`,`password`,`email`,`departmentId`) VALUES(?,?,?,?,? )";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1,user.getName());
			preparedStatement.setInt(2, user.getAge());
			preparedStatement.setString(3, user.getPassword());
			preparedStatement.setString(4, user.getEmail());
			preparedStatement.setInt(5, user.getDepartmentId());
			System.out.println(preparedStatement);
			count = preparedStatement.executeUpdate();
			System.out.println("count" + count);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
		
	}
	
	public int update(User user) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		int count = 0;
		
		try {
			connection = JDBCUtil.getConnection();
			String sql = "UPDATE `user` SET `name`=?,`age`=?,`password`=?,`email`=?,`department_id`=? WHERE `id`=?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, user.getName());
			preparedStatement.setInt(2, user.getAge());
			preparedStatement.setString(3, user.getPassword());
			preparedStatement.setString(4, user.getEmail());
			preparedStatement.setInt(5, user.getDepartmentId());
			preparedStatement.setInt(6, user.getId());
			System.out.println(preparedStatement);
			count = preparedStatement.executeUpdate();
			System.out.println("count: " + count);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}
	
	public User selectById(int id) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		User user = null;
		
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT `id`,`name`,`password`,`email`,`age`,`department_id` FROM `user` WHERE `id` =?";
			 preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, id);
			System.out.println(preparedStatement);
			resultSet = preparedStatement.executeQuery();
			
			while (resultSet.next()) {
				int userId = resultSet.getInt("id");
				String name = resultSet.getString("name");
				String password = resultSet.getString("password");
				String email = resultSet.getString("email");
				int age = resultSet.getInt("age");
				int departmentId = resultSet.getInt("department_id");
				
				user = new User(id, name, password, age, email, departmentId);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		System.out.println(user);
		
		return user;
	}
	
	public List<User> selectByPage(int offset, int pageSize) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<User> list = new ArrayList<User>();
		
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT `id`,`name`,`password`,`email`,`age`,`department_id` FROM `user` LIMIT ?,?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, offset);
			preparedStatement.setInt(2, pageSize);
			System.out.println(preparedStatement);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				String password = resultSet.getString("password");
				String email = resultSet.getString("email");
				int age = resultSet.getInt("age");
				int departmentId = resultSet.getInt("department_id");
				
				User user = new User(id, name, password, age, email, departmentId);
				list.add(user);
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		for (User user : list) {
			System.out.println(user);
	}
		return list;

}
	
	public int selectTotalCount() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		int totalCount = 0;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT count(*) FROM `user`";
			preparedStatement = connection.prepareStatement(sql);
			System.out.println(preparedStatement);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				// totalCount = resultSet.getInt("count(*)");
				totalCount = resultSet.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return totalCount;
	}
	
	
	public List<UserDeptVO> selectUserDeptInfo() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<UserDeptVO> list = new ArrayList<UserDeptVO>();
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT u.id,u.name,u.age,u.password,u.email,d.name AS deptName FROM `user` AS u INNER JOIN `department` AS d ON u.department_id=d.id";
			preparedStatement = connection.prepareStatement(sql);
			System.out.println(preparedStatement);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				String password = resultSet.getString("password");
				String email = resultSet.getString("email");
				int age = resultSet.getInt("age");
				String deptName = resultSet.getString("deptName");
				
				UserDeptVO userDeptVO = new UserDeptVO(id, name, password, age, email, deptName);
				list.add(userDeptVO);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		for (UserDeptVO userDeptVO : list) {
			System.out.println(userDeptVO);
		}
		
		return list;
	}
	
	
	
	public User login(String name, String password) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		User user = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "SELECT `id`,`name`,`password`,`email`,`age`,`department_id` FROM `user` WHERE `name`=? AND `password`=?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, name);
			preparedStatement.setString(2, password);
			System.out.println(preparedStatement);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int userId = resultSet.getInt("id");
				String userName = resultSet.getString("name");
				String userPassword = resultSet.getString("password");
				String email = resultSet.getString("email");
				int age = resultSet.getInt("age");
				int departmentId = resultSet.getInt("department_id");
				
				user = new User(userId, userName, userPassword, age, email, departmentId);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		System.out.println(user);
		
		return user;
	}
	
	
}
